import json
import re

def parse_sql_to_json(sql_file):
    # 读取SQL文件
    with open(sql_file, 'r', encoding='utf-8') as f:
        sql_content = f.read()

    # 分割成单独的CREATE TABLE语句
    create_tables = re.split(r'--\s*[^\n]*\n', sql_content)
    create_tables = [t.strip() for t in create_tables if t.strip()]

    # 解析每个表的结构
    tables = []
    for table_sql in create_tables:
        # 提取表名
        table_name_match = re.search(r'CREATE TABLE (\w+)', table_sql)
        if not table_name_match:
            continue
        
        table_name = table_name_match.group(1)
        
        # 提取字段定义
        fields = []
        # 修改正则表达式以更好地匹配字段定义
        field_matches = re.finditer(r'`?(\w+)`?\s+([^,]+?)(?:,|\))(?:\s+ENGINE|$)', table_sql)
        
        for match in field_matches:
            field_name = match.group(1)
            field_def = match.group(2).strip()
            
            # 改进字段类型解析
            type_match = re.search(r'([A-Za-z]+(?:\([^)]+\))?)', field_def)
            if not type_match:
                continue
                
            field_type = type_match.group(1)
            comment_match = re.search(r"COMMENT\s+'([^']*)'", field_def)
            comment = comment_match.group(1) if comment_match else None
            
            # 检查是否是主键
            is_primary = 'PRIMARY KEY' in field_def
            
            # 检查是否允许NULL
            is_nullable = 'NOT NULL' not in field_def
            
            # 检查是否有默认值
            default_match = re.search(r"DEFAULT\s+([^,\s]+)", field_def)
            default_value = default_match.group(1) if default_match else None
            
            field_info = {
                'name': field_name,
                'type': field_type,
                'nullable': is_nullable,
                'is_primary': is_primary
            }
            
            if comment:
                field_info['comment'] = comment
            if default_value:
                field_info['default'] = default_value
                
            fields.append(field_info)
        
        # 提取外键约束
        foreign_keys = []
        fk_matches = re.finditer(r'FOREIGN KEY\s*\(`?(\w+)`?\)\s*REFERENCES\s*`?(\w+)`?\s*\(`?(\w+)`?\)', table_sql)
        
        for match in fk_matches:
            fk = {
                'field': match.group(1),
                'references_table': match.group(2),
                'references_field': match.group(3)
            }
            foreign_keys.append(fk)
        
        table_info = {
            'name': table_name,
            'fields': fields,
            'foreign_keys': foreign_keys
        }
        
        tables.append(table_info)
    
    # 创建最终的JSON结构
    schema = {
        'database_name': '', #你的sql文件名称
        'tables': tables
    }
    
    return schema

def main():
    try:
        # 解析SQL文件
        schema = parse_sql_to_json('xxxx.sql') #你的sql文件
        
        # 将结果写入JSON文件
        output_file = 'database_schema.json'
        with open(output_file, 'w', encoding='utf-8') as f:
            json.dump(schema, f, ensure_ascii=False, indent=2)
        
        print(f"数据库结构已成功转换为JSON格式，并保存到 {output_file}")
        
    except Exception as e:
        print(f"发生错误: {str(e)}")
        raise

if __name__ == "__main__":
    main()